﻿Imports System.Data.OleDb

Public Class frmInventario

    Dim connexion As OleDbConnection
    Dim command As OleDbCommand

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        bd.Inventario("", "", "frmInventario")
        bd.Proveedor("", "frmInventario")
        bd.getCategoria("", "frmInventario", "grid")

        dgvInventario.RowHeadersVisible = False
        dgvInventario.Columns(2).Visible = False
        dgvInventario.Columns(4).Visible = False
        dgvInventario.Columns(6).Visible = False
        dgvInventario.Columns(8).Visible = False
        dgvInventario.Columns(9).Visible = False
        dgvInventario.ClearSelection()
        dgvInventario.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill

        dgvProveedor.RowHeadersVisible = False
        dgvProveedor.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        dgvProveedor.Columns(2).Visible = False
        dgvProveedor.Columns(3).Visible = False
        dgvProveedor.Columns(4).Visible = False
        dgvProveedor.Columns(5).Visible = False
        dgvProveedor.ClearSelection()

        dgvCategoria.RowHeadersVisible = False
        dgvCategoria.ClearSelection()
        dgvCategoria.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill

    End Sub

    Private Sub txtBuscar_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Inventario("", txtBuscar.Text, "frmInventario")
    End Sub

    Private Sub btnGardar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGardar.Click

        If btnGardar.Text = "Nuevo" Then
            limpiar()
            btnGardar.Text = "Guardar"
            btnActualizar.Enabled = False
            btnEliminar.Enabled = False
        Else
            connexion = New OleDbConnection(bd.sConexion)
            command = New OleDbCommand("INSERT INTO Inventario (idCodigoProducto,NombreProducto,IdProveedor,Marca,FechaVencimiento,IdCategoria,PorPeso,Cantidad,CantidadMinima,PrecioCompra,PrecioVenta)" &
                                       "values (@COD,@NOMPRODUCTO,@IDPROVEEDOR,@MARCA,@FECHAVENCIMIENTO," & CType(dgvCategoria.Item(0, dgvCategoria.CurrentRow.Index).Value, Integer) & ",@PRODPESO,@CANTEXISTENCIA,@CANTMINIMA,@PRECIOCOMPRA,@PRECIOVENTA ) ", connexion)

            command.Parameters.Add(New OleDbParameter("@COD", OleDbType.VarWChar, 20))
            command.Parameters("@COD").Value = txtCodigoProducto.Text
            command.Parameters.Add(New OleDbParameter("@NOMPRODUCTO", OleDbType.VarWChar, 20))
            command.Parameters("@NOMPRODUCTO").Value = txtNombreProducto.Text
            command.Parameters.Add(New OleDbParameter("@IDPROVEEDOR", OleDbType.Integer, 20))
            command.Parameters("@IDPROVEEDOR").Value = CType(dgvProveedor.Item(0, dgvProveedor.CurrentRow.Index).Value, Integer)
            command.Parameters.Add(New OleDbParameter("@MARCA", OleDbType.VarWChar, 20))
            command.Parameters("@MARCA").Value = txtMarca.Text
            command.Parameters.Add(New OleDbParameter("@FECHAVENCIMIENTO", OleDbType.Date))
            command.Parameters("@FECHAVENCIMIENTO").Value = dtpFechaVencimiento.Text
            command.Parameters.Add(New OleDbParameter("@PRODPESO", OleDbType.Boolean))
            If ckbPorPeso.Checked = True Then
                command.Parameters("@PRODPESO").Value = True
            Else
                command.Parameters("@PRODPESO").Value = False
            End If

            ' command.Parameters.Add(New OleDbParameter("@CATEG", OleDbType.Integer, 20))
            'command.Parameters("@CATEG").Value = CType(dgvCategoria.Item(0, dgvCategoria.CurrentRow.Index).Value, Integer)

            command.Parameters.Add(New OleDbParameter("@CANTMINIMA", OleDbType.Integer, 20))
            command.Parameters("@CANTMINIMA").Value = txtCantidadMinima.Text
            command.Parameters.Add(New OleDbParameter("@CANTEXISTENCIA", OleDbType.Integer, 20))
            command.Parameters("@CANTEXISTENCIA").Value = txtCantExistencia.Text
            command.Parameters.Add(New OleDbParameter("@PRECIOCOMPRA", OleDbType.Integer, 20))
            command.Parameters("@PRECIOCOMPRA").Value = txtPrecioCompra.Text
            command.Parameters.Add(New OleDbParameter("@PRECIOVENTA", OleDbType.Integer, 20))
            command.Parameters("@PRECIOVENTA").Value = txtPrecioVenta.Text()


            'mando sql a base de datos
            command.Connection.Open()
            Dim result As Integer = command.ExecuteNonQuery()
            command.Connection.Close()

            If result > 0 Then
                bd.Inventario("", "", "frmInventario")
                limpiar()
                MsgBox("Registro Insertado")
            End If
        End If
    End Sub

    Private Sub dgvProveedor_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvProveedor.CellClick
        txtProveedor.Text = dgvProveedor.Item(1, dgvProveedor.CurrentRow.Index).Value
    End Sub

    Private Sub txtBuscaProveedor_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtBuscaProveedor.TextChanged
        bd.Proveedor(txtBuscaProveedor.Text, "frmInventario")
        dgvProveedor.ClearSelection()
        dgvProveedor.Columns(0).Width = 120
        dgvProveedor.Columns(1).Width = 125
    End Sub

    Private Sub dgvCategoria_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvCategoria.CellClick
        txtCategoria.Text = dgvCategoria.Item(1, dgvCategoria.CurrentRow.Index).Value
    End Sub

    Private Sub txtxBuscaCategoria_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtBuscaCategoria.TextChanged
        bd.getCategoria(txtBuscaCategoria.Text, "frmInventario", "grid")
        dgvCategoria.ClearSelection()
        dgvCategoria.Columns(0).Width = 120
        dgvCategoria.Columns(1).Width = 125
    End Sub

    Private Sub txtBuscar_TextChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtBuscar.TextChanged
        bd.Inventario("", txtBuscar.Text, "frmInventario")
    End Sub

    Private Sub dgvInventario_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvInventario.CellClick


        txtCodigoProducto.Text = dgvInventario.Item(0, dgvInventario.CurrentRow.Index).Value
        If IsDBNull(dgvInventario.Item(1, dgvInventario.CurrentRow.Index).Value) Then
            txtNombreProducto.Text = ""
        Else
            txtNombreProducto.Text = dgvInventario.Item(1, dgvInventario.CurrentRow.Index).Value
        End If
        If IsDBNull(dgvInventario.Item(2, dgvInventario.CurrentRow.Index).Value) Then
            txtProveedor.Text = ""
        Else
            txtProveedor.Text = dgvInventario.Item(2, dgvInventario.CurrentRow.Index).Value
        End If
        If IsDBNull(dgvInventario.Item(3, dgvInventario.CurrentRow.Index).Value) Then
            txtMarca.Text = ""
        Else
            txtMarca.Text = dgvInventario.Item(3, dgvInventario.CurrentRow.Index).Value
        End If
        dtpFechaVencimiento.Text = dgvInventario.Item(4, dgvInventario.CurrentRow.Index).Value
        If IsDBNull(dgvInventario.Item(5, dgvInventario.CurrentRow.Index).Value) Then
            txtCategoria.Text = ""
        Else
            txtCategoria.Text = dgvInventario.Item(5, dgvInventario.CurrentRow.Index).Value
        End If
        If dgvInventario.Item(6, dgvInventario.CurrentRow.Index).Value = True Then
            ckbPorPeso.Checked = True
        Else
            ckbPorPeso.Checked = False
        End If
        If IsDBNull(dgvInventario.Item(7, dgvInventario.CurrentRow.Index).Value) Then
            txtCantidadMinima.Text = ""
        Else
            txtCantidadMinima.Text = dgvInventario.Item(7, dgvInventario.CurrentRow.Index).Value
        End If
        If IsDBNull(dgvInventario.Item(8, dgvInventario.CurrentRow.Index).Value) Then
            txtCantExistencia.Text = ""
        Else
            txtCantExistencia.Text = dgvInventario.Item(8, dgvInventario.CurrentRow.Index).Value
        End If
        If IsDBNull(dgvInventario.Item(9, dgvInventario.CurrentRow.Index).Value) Then
            txtPrecioCompra.Text = ""
        Else
            txtPrecioCompra.Text = dgvInventario.Item(9, dgvInventario.CurrentRow.Index).Value
        End If
        If IsDBNull(dgvInventario.Item(10, dgvInventario.CurrentRow.Index).Value) Then
            txtPrecioVenta.Text = ""
        Else
            txtPrecioVenta.Text = dgvInventario.Item(10, dgvInventario.CurrentRow.Index).Value
        End If

        btnGardar.Text = "Nuevo"
        btnActualizar.Enabled = True
        btnEliminar.Enabled = True


    End Sub

    Sub limpiar()
        txtBuscar.Clear()
        dgvInventario.ClearSelection()
        txtCodigoProducto.Clear()
        txtNombreProducto.Clear()
        txtProveedor.Clear()
        txtMarca.Clear()
        dtpFechaVencimiento.Text = Now
        txtCategoria.Clear()
        ckbPorPeso.Checked = False
        txtCantidadMinima.Clear()
        txtCantExistencia.Clear()
        txtPrecioCompra.Clear()
        txtPrecioVenta.Clear()
        txtBuscaCategoria.Clear()
        dgvCategoria.ClearSelection()
        txtBuscaProveedor.Clear()
        dgvProveedor.ClearSelection()

    End Sub

    Private Sub btnActualizar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnActualizar.Click
        connexion = New OleDbConnection(bd.sConexion)
        command = New OleDbCommand("UPDATE Inventario SET NombreProducto = '" & txtNombreProducto.Text & "', IdProveedor = " & dgvProveedor.Item(0, dgvProveedor.CurrentRow.Index).Value & ", Marca = '" & txtMarca.Text & "', FechaVencimiento = " & CDate(dtpFechaVencimiento.Text) & ", IdCategoria = " & CType(dgvCategoria.Item(0, dgvCategoria.CurrentRow.Index).Value, Integer) & ", PorPeso = " & ckbPorPeso.Checked & ", Cantidad = " & txtCantExistencia.Text & ", CantidadMinima = " & txtCantidadMinima.Text & ", PrecioCompra = " & txtPrecioCompra.Text & ", PrecioVenta = " & txtPrecioVenta.Text & " WHERE idCodigoProducto = @COD", connexion)

        command.Parameters.Add(New OleDbParameter("@COD", OleDbType.VarWChar, 20))
        command.Parameters("@COD").Value = txtCodigoProducto.Text()

        command.Parameters.Add(New OleDbParameter("@PRODPESO", OleDbType.Boolean))

        'mando sql a base de datos
        command.Connection.Open()
        Dim result As Integer = command.ExecuteNonQuery()
        command.Connection.Close()

        If result > 0 Then
            bd.Inventario("", "", "frmInventario")
            limpiar()
            MsgBox("Registro fue Actualizada")
        End If
    End Sub

    Private Sub btnEliminar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEliminar.Click
        connexion = New OleDbConnection(bd.sConexion)
        command = New OleDbCommand("DELETE FROM Inventario WHERE idCodigoProducto = @COD", connexion)

        command.Parameters.Add(New OleDbParameter("@COD", OleDbType.VarWChar, 20))
        command.Parameters("@COD").Value = txtCodigoProducto.Text()

        'mando sql a base de datos
        command.Connection.Open()
        Dim result As Integer = command.ExecuteNonQuery()
        command.Connection.Close()

        If result > 0 Then
            bd.Inventario("", "", "frmInventario")
            limpiar()
            MsgBox("El Registro fue Eliminado")
        End If
    End Sub

    Private Sub lblBuscar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lblBuscar.Click

    End Sub

    Private Sub dgvInventario_CellContentClick_1(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvInventario.CellContentClick

    End Sub
End Class
